create database OADatabase
go

use OADatabase
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeInfo') and o.name = 'FK_EMPLOYEE_REFERENCE_DEPARTME')
alter table EmployeeInfo
   drop constraint FK_EMPLOYEE_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeInfo') and o.name = 'FK_EMPLOYEE_REFERENCE_APPLYFOR')
alter table EmployeeInfo
   drop constraint FK_EMPLOYEE_REFERENCE_APPLYFOR
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Userinfo') and o.name = 'FK_USERINFO_REFERENCE_APPLYFOR')
alter table Userinfo
   drop constraint FK_USERINFO_REFERENCE_APPLYFOR
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ApplyFor')
            and   type = 'U')
   drop table ApplyFor
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeInfo')
            and   type = 'U')
   drop table EmployeeInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Userinfo')
            and   type = 'U')
   drop table Userinfo
go

/*==============================================================*/
/* Table: ApplyFor                                              */
/*==============================================================*/
create table ApplyFor (
   ApID                 int                  identity,
   UserID               nvarchar(80)         null,
   time                 datetime             null,
   Day                  int                  null,
   Cout                 int                  null,
   Aderss               nvarchar(80)         null,
   Budget               money                null,
   Accept               int                  null,
   constraint PK_APPLYFOR primary key (ApID)
)
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   DmID                 int                  identity,
   DmName               nvarchar(80)         null,
   constraint PK_DEPARTMENTINFO primary key (DmID)
)
go

/*==============================================================*/
/* Table: EmployeeInfo                                          */
/*==============================================================*/
create table EmployeeInfo (
   EmID                 int                  identity,
   EmName               nvarchar(80)         null,
   Position             nvarchar(80)         null,
   Phone                nvarchar(80)         null,
   Address              nvarchar(100)        null,
   Acceptloge           int                  null,
   DmID                 int                  null,
   constraint PK_EMPLOYEEINFO primary key (EmID)
)
go

/*==============================================================*/
/* Table: Userinfo                                              */
/*==============================================================*/
create table Userinfo (
   UserID               int                  not null,
   ApID                 int                  null,
   UserName             nvarchar(80)         null,
   Bank                 nvarchar(80)         null,
   Telephone            nvarchar(80)         null,
   Location             nvarchar(80)         null,
   constraint PK_USERINFO primary key (UserID)
)
go

alter table EmployeeInfo
   add constraint FK_EMPLOYEE_REFERENCE_DEPARTME foreign key (DmID)
      references DepartmentInfo (DmID)
go

alter table EmployeeInfo
   add constraint FK_EMPLOYEE_REFERENCE_APPLYFOR foreign key (Acceptloge)
      references ApplyFor (ApID)
go

alter table Userinfo
   add constraint FK_USERINFO_REFERENCE_APPLYFOR foreign key (ApID)
      references ApplyFor (ApID)
go

 